#! /bin/bash
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "

SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
set hive.exec.orc.compression.strategy=COMPRESSION;

DROP TABLE edu_dwb.dwb_purpose_clue_analyse;
CREATE TABLE edu_dwb.dwb_purpose_clue_analyse
(
-- 客户意向表字段
    id                INT           COMMENT '意向id',
    origin_type       STRING        COMMENT '数据来源',
    origin_channel    STRING        COMMENT '来源渠道',
-- 员工部门表
    dep_name          STRING        COMMENT '部门名称',
-- 校区信息表
    sch_name          STRING        COMMENT '校区名称',
-- 学科信息表
    sub_name          STRING        COMMENT '学科名称',
-- 客户静态信息表
    area              STRING        COMMENT '所在区域',
-- 线索申诉状态表
    appeal_status     STRING        COMMENT '申诉状态，0:待稽核 1:无效 2：有效',
-- 客户线索表
    clue_state        STRING        COMMENT '线索状态',
-- 客户意向表字段
    create_date_time  STRING        COMMENT '创建时间（申诉时间）',
    update_date_time  STRING        COMMENT '更新时间',
    create_time_year  STRING        COMMENT '年',
    create_time_month STRING        COMMENT '月',
    create_time_hour  STRING        COMMENT '小时'
)
COMMENT '意向用户统计宽表'
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

INSERT INTO TABLE edu_dwb.dwb_purpose_clue_analyse PARTITION (dt)
SELECT
    dfcr.id AS id,
    IF(origin_type='NETSERVICE' OR origin_type='PRESIGNUP','online','Offline') AS origin_type,
    origin_channel,
    ddsd.name AS dep_name,
    ddis.name AS sch_name,
    ddsu.name AS sub_name,
    area,
    IF(appeal_status=2,'true','others') AS appeal_status,
    IF(clue_state= 'VALID_NEW_CLUES','new_stu','old_stu'),
    dfcr.create_date_time AS create_date_time,
    dfcr.update_date_time AS update_date_time,
    create_time_year,
    create_time_month,
    create_time_hour,
    dt
FROM edu_dwd.dwd_fact_customer_relationship dfcr
LEFT JOIN edu_dwd.dwd_dim_employee dde ON dfcr.creator = dde.id
LEFT JOIN edu_dwd.dwd_dim_scrm_department  ddsd ON dde.tdepart_id=ddsd.id
LEFT JOIN edu_dwd.dwd_dim_itcast_school ddis ON dfcr.itcast_school_id=ddis.id
LEFT JOIN edu_dwd.dwd_dim_itcast_subject ddsu ON dfcr.itcast_subject_id=ddsu.id
LEFT JOIN edu_dwd.dwd_dim_customer ddc ON dfcr.customer_id=ddc.id
LEFT JOIN edu_dwd.dwd_dim_customer_appeal ddca ON dfcr.id=ddca.customer_relationship_first_id
LEFT JOIN edu_dwd.dwd_dim_customer_clue ddcc ON dfcr.id=ddcc.customer_relationship_id;